const router = require('koa-router')()
//财务管理模块
router.prefix('/finance')
//1.查看财务情况
router.get('/', async (ctx, next) => {
    //筛选收入或者支出
    let type = ctx.request.query.type;//收入或者支出
    let countSql = (type == "支出") ? `select count(*) from buy where buy_type = '已完成'` : `select count(*) from orders where order_type = '已完成'`;
    let countResults = await ctx.db.EXCUTE(countSql, []);
    let total = countResults[0]["count(*)"];
    let pageSize = Number(ctx.request.query.size)
    let page = ctx.request.query.page
    let startRow = total > 0 ? ((page - 1) * pageSize) : 0;
    let limitSql = (type == "支出") ? `select * from buy where buy_type = '已完成' order by buy_id desc limit ${startRow},${pageSize}` : `select * from orders where order_type = '已完成' order by order_id desc limit ${startRow},${pageSize}`;
    let results = await ctx.db.EXCUTE(limitSql, []);
    if (type == "支出") {
        for (let i = 0; i < results.length; i++) {
            if (results[i].buy_buyerid) {
                let sql3 = `select user_name from users where user_id = ${results[i].buy_buyerid}`;
                let res3 = await ctx.db.EXCUTE(sql3, []);
                let buyerName = res3[0].user_name;
                results[i].buyerName = buyerName;
                let sql4 = `select user_name from users where user_id = ${results[i].buy_storeid}`;
                let res4 = await ctx.db.EXCUTE(sql4, []);
                let storerName = res4[0].user_name;
                results[i].storerName = storerName;
            }
        }
    }
    if (type == "收入") {
        for (let i = 0; i < results.length; i++) {
            let sql = `select user_name from users where user_id = ${results[i].order_directorid}`;
            let res = await ctx.db.EXCUTE(sql, []);
            let directorName = res[0].user_name;
            results[i].directorName = directorName;
            if (results[i].order_plannerid) {
                let sql2 = `select user_name from users where user_id = ${results[i].order_plannerid}`;
                let res2 = await ctx.db.EXCUTE(sql2, []);
                let plannerName = res2[0].user_name;
                results[i].plannerName = plannerName;
            }
            if (results[i].order_customerid) {
                let sql3 = `select customer_company from customers where customer_id = ${results[i].order_customerid}`;
                let res3 = await ctx.db.EXCUTE(sql3, []);
                let customerCompany = res3[0].customer_company;
                results[i].customerCompany = customerCompany;
            }
        }
    }
    let obj = {
        results: results,
        status: 200,
        length: total
    }
    ctx.body = JSON.stringify(obj);
})

//筛选年份 获得12个月的月收入和月支出和月总收入支出
router.get('/financeLine', async (ctx, next) => {
    let selectedYear = ctx.request.query.year;
    let incomeTotal = 0;
    let outcomeTotal = 0;
    let incomeArr = [];
    let outcomeArr = [];
    for (let i = 1; i <= 12; i++) {
        let obj = {};
        obj.month = i;
        obj.money = 0;
        incomeArr.push(obj);
    }
    for (let i = 1; i <= 12; i++) {
        let obj = {};
        obj.month = i;
        obj.money = 0;
        outcomeArr.push(obj);
    }
    //收入--查询orders表
    let sql1 = `select * from orders`;
    let results1 = await ctx.db.EXCUTE(sql1, []);
    for (let i = 0; i < results1.length; i++) {
        let deadline = results1[i].order_posttime;
        let date = new Date(deadline);
        let currentYear = date.getFullYear();
        if (String(currentYear) == selectedYear) {
            let month = Number(date.getMonth()) + 1;
            let money = results1[i].order_money;
            incomeArr[month - 1].money += money;
            incomeTotal += money;
        }
    }
    //let arr = [{month:1,money:50}]
    //支出--查询buy表
    let sql2 = `select * from buy`;
    let results2 = await ctx.db.EXCUTE(sql2, []);
    for (let i = 0; i < results2.length; i++) {
        let deadline = results2[i].buy_posttime;
        let date = new Date(deadline);
        let currentYear = date.getFullYear();
        if (String(currentYear) == selectedYear) {
            let month = Number(date.getMonth()) + 1;
            let money = results2[i].buy_money;
            outcomeArr[month - 1].money += money;
            outcomeTotal += money;
        }
    }
    let obj = {
        incomeArr: incomeArr,
        outcomeArr: outcomeArr,
        incomeTotal: incomeTotal,
        outcomeTotal: outcomeTotal,
        status: 200
    }
    ctx.body = JSON.stringify(obj);

})
module.exports = router